package cc.idoone.codegen.table.query;

import cc.idoone.codegen.config.DataSourceConfig;
import cc.idoone.codegen.table.ColumnJavaType;
import cc.idoone.codegen.table.TableField;
import cc.idoone.codegen.table.TableInfo;
import cc.idoone.codegen.table.TableInfoQuery;
import cc.idoone.codegen.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @auther hemj
 * @since 2020/8/25
 */
public class OracleTableInfoQuery extends TableInfoQuery {
	public static final String tablesSql = "SELECT * FROM USER_TABLES";
	public static final String tableCommentsSql = "SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME = '%s'";
	public static final String tableFieldsSql = "SELECT A.COLUMN_NAME, CASE WHEN A.DATA_TYPE='NUMBER' THEN "
			+ "(CASE WHEN A.DATA_PRECISION IS NULL THEN A.DATA_TYPE "
			+ "WHEN NVL(A.DATA_SCALE, 0) > 0 THEN A.DATA_TYPE||'('||A.DATA_PRECISION||','||A.DATA_SCALE||')' "
			+ "ELSE A.DATA_TYPE||'('||A.DATA_PRECISION||')' END) "
			+ "ELSE A.DATA_TYPE END DATA_TYPE, B.COMMENTS,DECODE(C.POSITION, '1', 'PRI') KEY "
			+ "FROM USER_TAB_COLUMNS A INNER JOIN USER_COL_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME"
			+ " AND A.COLUMN_NAME = B.COLUMN_NAME LEFT JOIN USER_CONSTRAINTS D "
			+ "ON D.TABLE_NAME = A.TABLE_NAME AND D.CONSTRAINT_TYPE = 'P' "
			+ "LEFT JOIN USER_CONS_COLUMNS C ON C.CONSTRAINT_NAME = D.CONSTRAINT_NAME "
			+ "AND C.COLUMN_NAME=A.COLUMN_NAME WHERE A.TABLE_NAME = '%s' ORDER BY A.COLUMN_ID ";

	public OracleTableInfoQuery(DataSourceConfig dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public TableInfo queryTableInfo(String tableName) throws ClassNotFoundException, SQLException {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = getConn().prepareStatement(String.format(tableCommentsSql, tableName));
			ResultSet results = preparedStatement.executeQuery();
			TableInfo tableInfo;
			while (results.next()) {
				String name = results.getString("TABLE_NAME");
				String tableComment = results.getString("COMMENTS");
				if (tableName.equals(name)) {
					tableInfo = new TableInfo();
					tableInfo.setName(tableName);
					tableInfo.setComment(tableComment);
					tableInfo.setFields(getTableFields(tableName));
					return tableInfo;
				}
			}
			System.out.println("数据表不存在 " + tableName);
			return null;
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
			} catch (SQLException e) {
			}
		}
	}

	/**
	 * 获取数据库表字段信息
	 */
	private List<TableField> getTableFields(String tableName) throws SQLException, ClassNotFoundException {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = getConn().prepareStatement(String.format(tableFieldsSql, tableName));
			ResultSet results = preparedStatement.executeQuery();
			List<TableField> fieldList = new ArrayList<>();
			// 只设置一个主键，并放到最前面
			boolean foundKey = false;
			while (results.next()) {
				TableField field = new TableField();
				field.setName(results.getString("COLUMN_NAME"));
				field.setType(results.getString("DATA_TYPE"));
				field.setComment(results.getString("COMMENTS"));
				field.setPropertyName(StringUtils.underlineToCamel(field.getName()));
				field.setColumnType(getJavaType(field.getType()));
				if (field.getName().startsWith("field_") || field.getName().startsWith("FIELD_")) {
					continue; // 备用字段
				}
				// PRIMARY KEY
				String key = results.getString("KEY");
				if (!foundKey && StringUtils.isNotEmpty(key) && key.toUpperCase().equals("PRI")) {
					field.setPrimaryKey(true);
					foundKey = true;
				}
				if (field.isPrimaryKey())
					fieldList.add(0, field);
				else
					fieldList.add(field);
			}
			return fieldList;
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
			} catch (SQLException e) {
			}
		}
	}

	/**
	 * 获取数据库字段对应的JAVA类型
	 */
	private ColumnJavaType getJavaType(String fieldType) {
		String t = fieldType.toUpperCase();
		if (t.contains("CHAR")) {
			return ColumnJavaType.STRING;
		} else if (t.contains("DATE") || t.contains("TIMESTAMP")) {
			return ColumnJavaType.DATE;
		} else if (t.contains("NUMBER")) {
			if (t.matches("NUMBER\\(+\\d\\)")) {
				return ColumnJavaType.INTEGER;
			} else if (t.matches("NUMBER\\(+\\d{2}+\\)")) {
				return ColumnJavaType.LONG;
			}
			return ColumnJavaType.DOUBLE;
		} else if (t.contains("FLOAT")) {
			return ColumnJavaType.FLOAT;
		} else if (t.contains("clob")) {
			return ColumnJavaType.CLOB;
		} else if (t.contains("BLOB")) {
			return ColumnJavaType.OBJECT;
		} else if (t.contains("binary")) {
			return ColumnJavaType.BYTE_ARRAY;
		} else if (t.contains("RAW")) {
			return ColumnJavaType.BYTE_ARRAY;
		}
		return ColumnJavaType.STRING;
	}
}
